﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
//增删改查示例
namespace 连接数据库
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void queryData()
        {
            //连接到数据库
            string connStr = "Data Source=LAPTOP-PB2VD7DC;Initial Catalog=student;User ID=sa;Password=123456";
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //编写查询语句 查询的是某个表
                string queryStr = "select count(*) from student_inf";
                using (SqlCommand cmd = new SqlCommand(queryStr, connection))
                {
                    connection.Open();

                    object result = cmd.ExecuteScalar();
                    int r = Convert.ToInt32(result);
                    connection.Close();
                    Console.WriteLine("表中一共有{0}行", r);
                }
            }
        }

        //改
        private void updateData()
        {
            //连接到数据库
            string connStr = "Data Source=LAPTOP-PB2VD7DC;Initial Catalog=student;User ID=sa;Password=123456";
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //编写更新语句 找到要更新的数据库中的表即可
                string updateStr = "update student_inf set age = 15 where id = 2";
                using (SqlCommand cmd = new SqlCommand(updateStr, connection))
                {
                    connection.Open();
                    //执行非查询命令 返回影响的行数
                    int r = cmd.ExecuteNonQuery();
                    connection.Close();
                    Console.WriteLine("成功修改了{0}行", r);
                }
            }
        }


        //删
        private void deleteData()
        {
            //连接到数据库
            string connStr = "Data Source=LAPTOP-PB2VD7DC;Initial Catalog=student;User ID=sa;Password=123456";
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //编写插入语句 找到要插入的数据库中的表即可
                string deleteStr = "delete from student_inf where id = 2";
                using (SqlCommand cmd = new SqlCommand(deleteStr, connection))
                {
                    connection.Open();
                    //执行非查询命令 返回影响的行数
                    int r = cmd.ExecuteNonQuery();
                    connection.Close();
                    Console.WriteLine("成功删除了{0}行", r);
                }
            }
        }

        //增
        private void insertData()
        {
            //连接到数据库
            string connStr = "Data Source=LAPTOP-PB2VD7DC;Initial Catalog=student;User ID=sa;Password=123456";
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //编写插入语句 找到要插入的数据库中的表即可
                string insertStr = "insert into student_inf(name, age, department, className) values('张三', 12, '小学', '四年级')";
                using (SqlCommand cmd = new SqlCommand(insertStr, connection))
                {
                    connection.Open();
                    //执行非查询命令 返回影响的行数
                    int r = cmd.ExecuteNonQuery();
                    connection.Close();
                    Console.WriteLine("成功插入{0}行", r);
                }
            }
        }

        private void connect_Click(object sender, EventArgs e)
        {
            //新建连接对象
            SqlConnection connection = new SqlConnection();
            //给连接对象指定连接的参数（连接字符串）
            //Data Source: 数据源 为服务器名称
            //Initial Catalog:用来说明 要操作哪个数据库 sa 为默认的用户名
            connection.ConnectionString = "Data Source=LAPTOP-PB2VD7DC;Initial Catalog=student;User ID=sa;Password=123456";
            //开始建立连接
            connection.Open();//open 函数用于打开数据库连接
            //输出提示 State 表示状态
            if (connection.State == ConnectionState.Open)
            {
                Console.WriteLine("数据库已连接");
            }
            else
            {
                Console.WriteLine("数据库连接失败");
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
           
        }

        private void button2_Click(object sender, EventArgs e)
        {
            insertData();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            deleteData();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            updateData();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            queryData();
        }

        private void query2_Click(object sender, EventArgs e)
        {
            //加载配置文件 该配置文件在 App.config 中
            string connectStr = ConfigurationManager.AppSettings["ConnectionString"];
            //连接数据库 这一步要确定数据库是否在运行
            using (SqlConnection connection = new SqlConnection(connectStr))
            {
                //编写查询语句 查询的是某个表  编写语句
                string queryStr = "select count(*) from student_inf";
                using (SqlCommand cmd = new SqlCommand(queryStr, connection))
                {
                    //执行
                    connection.Open();

                    object result = cmd.ExecuteScalar();
                    int r = Convert.ToInt32(result);
                    //释放
                    connection.Close();
                    //得到结果
                    Console.WriteLine("表中一共有{0}行", r);
                }
            }
        }
    }
}
